alter procedure [dbo].[ReportMailState]
@deptNo varchar(50)
as
declare @resultSet table(
    deptName varchar(50),
    subNotSignNum int default 0,
    subNotReplyNum int default 0,
    subBackNum int default 0,
    subBlueNum int default 0
)
declare @deptName varchar(50)
declare @subNotSignNum int
declare @subNotReplyNum int
declare @subBackNum int
declare @subBlueNum int
declare @subDeptNo varchar(50)
declare @subDeptName varchar(50)
begin
    if len(@deptNo)>3 AND left(@deptNo,3)='304' set @deptNo=stuff(@deptNo,1,3,'301')
    declare subDeptSet cursor for
        select NoUnit subDeptNo,DeptShort subDeptName
        from bUserDept
        where isDel=0 and useState = 1 and NoParent=@deptNo
        order by OrdNum
    open subDeptSet
        fetch from subDeptSet into @subDeptNo,@subDeptName
        while (@@fetch_status = 0)
            begin
                select @deptName = @subDeptName
                select @subNotSignNum=sum(case when SendState in (10,11) then 1 else 0 end),
                       @subNotReplyNum=sum(case when SendState in (21,23) and BackState in (0,12,22) then 1 else 0 end),
                       @subBackNum=sum(case when SendState = 40 then 1 else 0 end),
                       @subBlueNum=sum(case when SendState in (21,23) and BackState in (0,12,22) and datediff(day,getdate(),BackLimit)<0 then 1 else 0 end)+sum(case when SendState in (10,11) and datediff(day,getdate(),BackLimit)<0 then 1 else 0 end)
                from mWfSendBack where isDel=0 and dbo.frMailWFStep(WfId)>399 and SBDeptID=@subDeptNo
                    if @subNotSignNum is null set @subNotSignNum = 0
                    if @subNotReplyNum is null set @subNotReplyNum = 0
                    if @subBackNum is null set @subBackNum = 0
                    if @subBlueNum is null set @subBlueNum = 0
                insert into @resultSet(deptName,subNotSignNum,subNotReplyNum,subBackNum,subBlueNum) values(@deptName,@subNotSignNum,@subNotReplyNum,@subBackNum,@subBlueNum)
            fetch next from subDeptSet into @subDeptNo,@subDeptName
            end
    close subDeptSet
    deallocate subDeptSet
select distinct * from @resultSet
end